package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import connection.ConnectionManager;

import domain.Video;

public class JDBCVideoDAO implements IVideoDAO {
	
	Connection conn;
	
	/**
	 * return all videos
	 */
	@Override
	public List<Video> getAllVideos() {
		List<Video> videos = new LinkedList<Video>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video");
			Video video = null;
			while(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
				videos.add(video);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}
	
	/**
	 * return all videos with the specified parameter
	 */
	@Override
	public List<Video> getAllVideosByGenre(String genre) {
		List<Video> videos = new LinkedList<Video>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video WHERE genero = '" + genre + "'");
			Video video = null;
			while(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));				videos.add(video);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}
	
	/**
	 * return all videos starting with the specified letter
	 */
	@Override
	public List<Video> getAllVideosStartingWithLetter(char letter) {
		List<Video> videos = new LinkedList<Video>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video WHERE titulo LIKE '" + letter + "%'");
			Video video = null;
			while(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));				videos.add(video);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}
	
	/**
	 * return the video with the specified title
	 */
	@Override
	public Video getVideoByTitle(String title) {
		Video video = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video WHERE titulo = '" + title + "'");
			if(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return video;
	}

	@Override
	public String getTitleById(Integer idvid) {
		String video = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT titulo FROM video WHERE vid = " + idvid + "");
			if(rs.next()){
				video = rs.getString(1);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return video;
	}

	@Override
	public Video getVideoById(Integer id) {
		Video video = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video WHERE vid = '" + id + "'");
			if(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return video;
	}

	@Override
	public List<Video> getAllVideosByTitleYearGenre(String title, Integer year,String genre) {
		List<Video> videos = new LinkedList<Video>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video WHERE titulo LIKE '%" + title + "%' AND anno ='"+year+"' AND genero LIKE'%"+genre+"%'");
			Video video = null;
			while(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));				videos.add(video);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}

	@Override
	public List<Video> getAllVideosByTitleYear(String title, Integer year) {
		List<Video> videos = new LinkedList<Video>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video WHERE titulo LIKE '%" + title + "%' AND anno ='"+year+"'");
			Video video = null;
			while(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));				videos.add(video);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}

	@Override
	public List<Video> getAllVideosByTitleGenre(String title, String genre) {
		List<Video> videos = new LinkedList<Video>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video WHERE titulo LIKE '%" + title + "%' AND genero LIKE'%"+genre+"%'");
			Video video = null;
			while(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));				videos.add(video);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}

	@Override
	public List<Video> getAllVideosByTitle(String title) {
		List<Video> videos = new LinkedList<Video>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video WHERE titulo LIKE '%" + title + "%'");
			Video video = null;
			while(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));				videos.add(video);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}

	@Override
	public List<Video> getAllVideosByYearGenre(Integer year, String genre) {
		List<Video> videos = new LinkedList<Video>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video WHERE anno ='"+year+"' AND genero LIKE'%"+genre+"%'");
			Video video = null;
			while(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));				videos.add(video);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}

	@Override
	public List<Video> getAllVideosByYear(Integer year) {
		List<Video> videos = new LinkedList<Video>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video WHERE anno ='"+year+"'");
			Video video = null;
			while(rs.next()){
				video = new Video(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));				videos.add(video);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}
}
